package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.data.models.Lsh精品信息Model;
import com.iris.live.services.data.repositories.AfterMarketRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.AfterMarketService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by sxy on 2016/8/1
 */
@Repository
@Transactional//(readOnly = true)
public class AfterMarketRepositoryImpl extends BaseRepository implements AfterMarketRepository {
    private static Logger logger = LoggerFactory.getLogger(AfterMarketRepositoryImpl.class);

    // 经销商	后市场	采集/自采产品
    @Override
    public PageData getProductList(int pageSize, int pageIndex, String permit,
                                   int type, int classify, String source, String product, String productCode, int NC,
                                   String area, String car, int productID, String factory,String totalCount,String carSource,String brand,
                                   String series,String carType,String carParticulars) {
        Map params = Maps.newHashMap();
        //params.put("permit", permit);
        String sql = "select IF(DATE_FORMAT(a.`精品结束日期`,'%Y%m%d') >= DATE_FORMAT(CURDATE(),'%Y%m%d'),1, 0) SORT_FLAG, " +
                " IF(DATE_FORMAT(a.`精品结束日期`,'%Y%m%d') >= DATE_FORMAT(CURDATE(),'%Y%m%d'),a.更新时间, a.精品结束日期) SORT_DATE,  " +
                "  a.精品编号,a.采购来源,c.NC分类,c.NC编号,d.产品种类,d.种类编号,b.产品分类,b.分类编号,a.产品名称\n" +
                ",a.产品编号,a.标配功能,a.厂商,a.经销商采购价含税 as '经销商采购价格（含安装费，含税）',a.经销商采购价未税 as '经销商采购价格（含安装费，未税）',a.经销商采购毛利未税 as '经销商毛利（未税）',a.经销商销售价含税 as '建议指导零售价 （含税）'\n" +
                ",a.经销商销售价未税 as '建议指导零售价 （未税）',a.经销商销售毛利率未税 as '经销商毛利率%（未税）'," +
                "a.集团采购价含税 as '精品采购价（含安装费，含税）',a.集团采购价未税 as '精品采购价（含安装费，未税）',a.集团采购毛利率 as '精品毛利率%',a.更新时间,a.创建时间,a.更新账号,a.启用状态,a.精品开始日期 as 开始日期,a.精品结束日期 as 结束日期 " +
                "from LSH精品信息 a\n" +
                "left join LSH精品分类 b on a.分类编号 = b.分类编号\n" +
                "left join LSH精品NC分类 c on a.NC编号 = c.NC编号\n" +
                "left join LSH精品种类 d on b.种类编号 = d.种类编号\n" ;

        if (carSource != null || brand != null || series != null || carType != null || carParticulars != null) {
           sql = sql+ "join  LSH精品适用车型   f on  f.精品编号 = a.精品编号\n" +
                    "JOIN(select z.车型编号  ,z.品牌  from \n" +
                    " LSH车型 z  JOIN LSH车型 m ON   \n" +
                    "(   m.来源=z.来源 and  m.品牌=z.品牌  and  m.级别 =z.级别  and    m.车型  = z.车型  and m.车型详细=z.车型详细)\n" +
                    " or (  m.来源=z.来源 and  m.品牌=z.品牌  and  m.级别 =z.级别  and  m.车型  = z.车型  and ''=z.车型详细)\n" +
                    " or ( m.来源=z.来源 and  m.品牌=z.品牌   and  m.级别 =z.级别  and  ''  = z.车型  and ''=z.车型详细)\n" +
                    " or (  m.来源=z.来源 and  m.品牌=z.品牌   and  '' =z.级别 and  ''  = z.车型   and  ''=z.车型详细)\n" +
                    " or (m.来源=z.来源 and   ''=z.品牌  and  '' =z.级别 and  '' = z.车型  and ''=z.车型详细)\n" +
                    " where   1=1 \n";

              if(carSource!=null) {
                  sql += "and   m.来源 = :carSource    \n";
                  params.put("carSource", carSource);
              }
              if(brand!=null){
                  sql += " and  m.品牌 = :brand    \n";
                  params.put("brand", brand);
              }
              if(series!=null){
                  sql += " and m.级别 = :series    \n";
                  params.put("series", series);
              }
              if(carType!=null){
                  sql += " and m.车型 = :carType    \n";
                  params.put("carType", carType);
              }
              if(carParticulars!=null){
                  sql += " and m.车型详细 = :carParticulars    \n";
                  params.put("carParticulars", carParticulars);
              }
                sql +=  " GROUP BY  z.车型编号\n";
                sql +=  " )  z on  f.车型编号 = z.车型编号  \n";
        }
       sql = sql + " where   1=1 \n";
        if (type != 0) {
            sql += "and a.分类编号 = :type \n";
            params.put("type", type);
        }
        if (classify != 0) {
            sql += "and b.种类编号 = :classify \n";
            params.put("classify", classify);
        }
        if (!Strings.isNullOrEmpty(source)) {
            //TJLS,permit
            if (source.contains(",")) {
                String[] arr = source.split(",");
                if ("TJLS".equals(arr[0])) {
                    sql += "and (a.采购来源 = :source or a.采购来源='TJLS') \n";
                    params.put("source", arr[1]);
                } else {
                    sql += "and (a.采购来源 = :source or a.采购来源='TJLS') \n";
                    params.put("source", arr[0]);
                }
            } else {
                sql += "and a.采购来源 = :source \n";
                params.put("source", source);

            }
        }

        if (!Strings.isNullOrEmpty(product)) {
            sql += "and a.产品名称 like :product \n";
            params.put("product", "%" + product + "%");
        }
        if (!Strings.isNullOrEmpty(productCode)) {
            sql += "and a.产品编号 like :productCode \n";
            params.put("productCode", "%" + productCode + "%");
        }
        if (NC != 0) {
            sql += "and c.NC编号 = :NC \n";
            params.put("NC", NC);
        }
        if (area != null) {
            sql += "and exists(select 1 from LSH精品适用区域 e where e.精品编号 = a.精品编号 and e.适用区域 like :area) \n";
            params.put("area", "%" + area + "%");
        }
        /*
        if (brand != null) {
            sql += "and exists(select 1 from LSH精品适用车型 f " +
                    "                   join LSH车型 z on f.车型编号 = z.车型编号  " +
                    "                   JOIN LSH车型 m ON   (   m.来源=z.来源 and  m.品牌=z.品牌  and  m.级别 =z.级别  and    m.车型  = z.车型  and m.车型详细=z.车型详细)\n" +
                    "                   or (  m.来源=z.来源 and  m.品牌=z.品牌  and  m.级别 =z.级别  and  m.车型  = z.车型  and ''=z.车型详细)\n" +
                    "                   or ( m.来源=z.来源 and  m.品牌=z.品牌   and  m.级别 =z.级别  and  ''  = z.车型  and ''=z.车型详细)\n" +
                    "                   or (  m.来源=z.来源 and  m.品牌=z.品牌   and  '' =z.级别 and  ''  = z.车型   and  ''=z.车型详细)\n" +
                    "                   or (m.来源=z.来源 and   ''=z.品牌  and  '' =z.级别 and  '' = z.车型  and ''=z.车型详细) " +
                    "                       where f.精品编号 = a.精品编号 and (m.品牌 like :brand or m.车型 like :brand or m.级别 like :brand or m.车型详细 like :brand)) \n";
            params.put("brand", "%" + brand + "%");
        }
        */


        if (productID != 0) {
            sql += "and a.精品编号 =  :productID \n";
            params.put("productID", productID);
        }
        if (!Strings.isNullOrEmpty(factory)) {
            sql += "and a.厂商 like  :factory \n";
            params.put("factory", "%" + factory + "%");
        }
       /* if ((!Strings.isNullOrEmpty(brand))){
            sql +="and a.";
        }
        */

        sql += " ORDER BY SORT_FLAG DESC, SORT_DATE DESC ";
        params.put("totalCount", totalCount == null ? 0 : totalCount );

        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public List getProductPic(int productID) {
        Map params = Maps.newHashMap();
        String sql = "select 图片编号,图片路径 from LSH精品图片 where 精品编号 = :productID";
        params.put("productID", productID);
        return this.getList(sql, params);
    }

    @Override
    public List getProductCar(int productID) {
        Map params = Maps.newHashMap();
        String sql = "select a.适用车型编号,a.车型编号,b.车型 from LSH精品适用车型 a\n" +
                "join LSH车型 b on a.车型编号 = b.车型编号\n" +
                "where 精品编号 = :productID";
        params.put("productID", productID);
        return this.getList(sql, params);
    }

    @Override
    public List getProductArea(int productID) {
        Map params = Maps.newHashMap();
        String sql = "select 适用区域编号,适用区域 from LSH精品适用区域 where 精品编号 = :productID";
        params.put("productID", productID);
        return this.getList(sql, params);
    }

    @Override
    public List getProductClassify(int type, String permit) {
        Map params = Maps.newHashMap();
        String sql = "select 分类编号,产品分类 from LSH精品分类 where 种类编号 = :type";
        /*String sql = " SELECT DISTINCT (g.`产品分类`),a.分类编号 " +
                " FROM  LSH套餐信息 a  " +
                " LEFT JOIN LSH精品套餐详细 b USING (套餐编号)  " +
                " LEFT JOIN LSH精品信息 c USING (精品编号)  " +
                " LEFT JOIN LSH精品促销 d ON c.`精品编号` = d.`精品编号` AND c.`采购来源` = d.许可  " +
                " LEFT JOIN LSH精品适用车型 e ON c.`精品编号` = e.`精品编号`  " +
                " LEFT JOIN LSH精品适用区域 f ON c.`精品编号` = f.`精品编号`  " +
                " LEFT JOIN LSH精品分类 g ON c.`分类编号` = g.`分类编号`  " +
                " LEFT JOIN LSH精品种类 h ON g.`种类编号` = h.`种类编号`  " +
                " LEFT JOIN `LSH车型` z USING (`车型编号`)  " +
                " WHERE  a.`是否套餐` = '否'  " +
                " AND c.`启用状态` = '启用'  " +
                " and h.`种类编号` = :type     " +
                " AND (c.采购来源= :permit OR c.采购来源='TJLS') " +
                " AND CURDATE()<=  c.`精品结束日期`   " +
                " AND CURDATE() >= c.`精品开始日期`   GROUP BY a.`分类编号` ";*/
        params.put("type", type);
       // params.put("permit", permit);
        return this.getList(sql, params);
    }

    @Override
    public List getProductType(Integer queryType) {
        String sql = null;
        switch (queryType){
            case 1:
                    sql = "select 种类编号,产品种类 from LSH精品种类 where 种类编号 = '1'";
                break;
            default:
                sql = "select 种类编号,产品种类 from LSH精品种类 where 种类编号 <> '1'";
        }
        return this.getList(sql);
    }

    @Override
    public List getNCType() {
        String sql = "select NC编号,NC分类 from LSH精品NC分类";
        return this.getList(sql);
    }

    // 后市场套餐查询
    @Override
    public PageData getProductCombo(int pageSize, int pageIndex, String permit, String comboID, String comboName, String area,String factory, String createUser
                                    ,String carSource,String brand,String series,String carType,String carParticulars) {
        String sql = "select a.套餐编号,c.NC分类,c.NC编号,d.产品种类,d.种类编号,b.产品分类,b.分类编号,a.产品名称 as 套餐名称\n" +
                ",a.产品编号,a.厂商,a.创建方,a.集团采购价未税 as 'TJLS采购价（含安装费，未税）',a.集团采购价含税 as 'TJLS采购价（含安装费，含税）',a.集团毛利率 as 'TJLS毛利率%',a.套餐销售原价\n" +
                ",a.套餐销售价含税 as '建议指导零售价 （含税）',a.套餐销售价未税 as '建议指导零售价 （未税）',a.是否统一折扣,a.套餐折扣,a.套餐采购价未税 as '经销商采购价格（含安装费，未税）',a.套餐采购价含税 as '经销商采购价格（含安装费，含税）',a.套餐毛利 as '经销商毛利（含税）'\n" +
                ",a.功能,a.特征,a.好处,a.是否套餐,a.限量状态,a.限量数量,a.套餐开始日期,a.套餐结束日期\n" +
                ",a.套餐毛利率 as '经销商毛利率%（含税）',a.更新账号,a.更新时间,a.创建时间,a.启用状态 from LSH套餐信息 a\n" +
                "join LSH精品套餐详细 detail on a.套餐编号 = detail.套餐编号\n" +
                "left join LSH精品分类 b on a.分类编号 = b.分类编号\n" +
                "left join LSH精品NC分类 c on a.NC编号 = c.NC编号\n" +
                "left join LSH精品种类 d on b.种类编号 = d.种类编号\n" +
                "where a.是否套餐 = '是' ";
        Map params = Maps.newHashMap();
        //params.put("permit", permit);
        if (!Strings.isNullOrEmpty(comboID)) {
            sql += "and a.产品编号 like :comboID \n";
            params.put("comboID", "%" + comboID + "%");
        }
        if (!Strings.isNullOrEmpty(area)) {
            sql += "and exists(select 1 from LSH套餐适用区域 e where e.套餐编号 = a.套餐编号 and e.适用区域 like :area)  \n";
            params.put("area", "%" + area + "%");
        }

            sql += "and exists(select 1 from LSH套餐适用车型 f join LSH车型 z on f.车型编号 = z.车型编号 where f.套餐编号 = a.套餐编号     \n";
        if (!Strings.isNullOrEmpty(carSource)){
            sql +=" and z.来源 = :carSource \n";
        params.put("carSource", carSource);
        }
        if(!Strings.isNullOrEmpty(brand)){
            sql +=" and z.品牌 = :brand \n";
            params.put("brand",  brand  );
        }
        if(!Strings.isNullOrEmpty(series)){
            sql +=" and z.级别 = :series \n";
            params.put("series", series);
        }
        if(!Strings.isNullOrEmpty(carType)){
            sql +=" and z.车型 = :carType \n";
            params.put("carType",  carType);
        }
        if(!Strings.isNullOrEmpty(carParticulars)){
            sql +=" and z.车型详细 = :carParticulars \n";
            params.put("carParticulars", carParticulars );
        }
        sql +=" ) \n";
      /*  if (!Strings.isNullOrEmpty(car)) {
            sql += "and exists(select 1 from LSH套餐适用车型 f join LSH车型 z on f.车型编号 = z.车型编号 where f.套餐编号 = a.套餐编号 and (z.品牌 like :car or z.级别 like :car OR z.车型 like :car or z.车型详细 like :car))   \n";
            params.put("car", "%" + car + "%");
        }
        */
        if (!Strings.isNullOrEmpty(comboName)) {
            sql += "and a.产品名称 like :comboName \n";
            params.put("comboName", "%" + comboName + "%");
        }
        if (!Strings.isNullOrEmpty(factory)) {
            sql += "and a.厂商 like  :factory \n";
            params.put("factory", "%" + factory + "%");
        }
        if (!Strings.isNullOrEmpty(createUser)) {
            //如果参数包含“,”
            if (createUser.contains(",")) {
                String[] arr = createUser.split(",");
                if ("RMB".equals(arr[0])) {
                    sql += "and (a.创建方 =  :createUser or a.创建方='RMB') \n";
                    params.put("createUser", arr[1]);
                } else {
                    sql += "and (a.创建方 =  :createUser or a.创建方='RMB') \n";
                    params.put("createUser", arr[0]);
                }
            } else {
                sql += "and a.创建方 =  :createUser \n";
                params.put("createUser", createUser);

            }
        }
        sql += "group by a.套餐编号 ";
        sql += "order by a.更新时间  desc";
        return this.getPage(sql, params, pageSize, pageIndex);
    }

    @Override
    public List getProductComboPic(int comboID) {
        Map params = Maps.newHashMap();
        String sql = "select 图片编号,图片路径 from LSH套餐图片 where 套餐编号 = :comboID";
        params.put("comboID", comboID);
        return this.getList(sql, params);
    }

    @Override
    public List getProductComboArea(int comboID) {
        Map params = Maps.newHashMap();
        String sql = "select a.适用车型编号,a.车型编号,b.车型 from LSH套餐适用车型 a\n" +
                "join LSH车型 b on a.车型编号 = b.车型编号\n" +
                "where 套餐编号 = :comboID";
        params.put("comboID", comboID);
        return this.getList(sql, params);
    }

    @Override
    public List getProductComboCar(int comboID) {
        Map params = Maps.newHashMap();
        String sql = "select 适用区域编号,适用区域 from LSH套餐适用区域 where 套餐编号 = :comboID";
        params.put("comboID", comboID);
        return this.getList(sql, params);
    }

    @Override
    public List getProductByComboID(int comboID) {
        String sql = "select a.精品编号,detail.精品数量,detail.精品折扣 from LSH精品信息 a \n" +
                "join LSH精品套餐详细 detail on a.精品编号 = detail.精品编号  \n" +
                "where detail.套餐编号 = :comboID";
        Map params = Maps.newHashMap();
        params.put("comboID", comboID);
        return this.getList(sql, params);
    }

    /*
        接口修改:
        返回参数的 变化 和 筛选条件 的 变化
      11.11 由于 _精品表的 变化 导致 返回的 必须有  有 _精品 的 主键id
    */
    @Override
    public PageData getAfterMarketOrder(String permit, AfterMarketService.GetAfterMarketOrderRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT " +
                "		CASE  " +
                "			WHEN NOT ISNULL(i.`交车日期`) THEN '交车完毕'  " +
                "			WHEN (NOT ISNULL(i.`开票时间`) AND ISNULL(i.`退票时间`)) OR (NOT ISNULL(i.`开票时间`) AND NOT ISNULL(i.`退票时间`) AND i.`开票时间` > i.`退票时间`) THEN '零售'  " +
                "			WHEN NOT ISNULL(i.`退票时间`) AND o.`订金类型` = '订金' AND ISNULL(o.`订单失销日期`) THEN '订金'  " +
                "			WHEN NOT ISNULL(p.`应付金额`) AND NOT ISNULL(p.`实付金额`) AND ROUND(p.`实付金额`, 4) >= ROUND(p.`应付金额`, 4) THEN '全款到账'  " +
                "			WHEN NOT ISNULL(i.`合同签约日期`) THEN '合同'  " +
                "			WHEN NOT ISNULL(o.`订单审批通过日期`) AND  ISNULL(o.`订单失销日期`) AND o.`订金类型`='订金' THEN '订金'  " +
                "			WHEN NOT ISNULL(o.`订单审批通过日期`) AND  ISNULL(o.`订单失销日期`) AND o.`订金类型`='意向金' THEN '意向金'  " +
                "			ELSE '报价'  " +
                "		END AS 新车销售状态,  " +
                "		a.id,  a.contract_id,  a.`施工种类`,  c.许可 as 经销商代码,  IF(d.`折后价` = 0,'赠送','销售') as 结账方式,  j.经销商名 as 经销商简称,  " +
                "		j.经销商全称 as 经销商名称, k.所属部门 as 申请部门,  k.人员姓名 as 销售顾问,  k.人员电话 as 手机号码, k.座机号码," +
                "		i.`开票客户姓名` as `车主/开票人姓名`,  h.`车型` as 意向车型,  h.`车型详细` as 意向车款, h.`品牌`, g.`颜色`, g.`内饰`,  " +
                "		g.生产号, g.车架号 as `车架号（VIN）`,  l.人员姓名 as 申请人,  f.采购来源, if(f.`采购来源` = 'TJLS', IFNULL(a.order_status, 1), '') as 订单状态,  " +
                "		f.厂商 as 后市场厂商,  e.是否套餐,  F.产品名称 as 后市场产品名称,  F.产品编号 as 后市场产品编号,  " +
                "		if(e.是否套餐 = '否', d.`折后价`, round(((f.`经销商销售价含税` * b.`精品折扣` / 100) * (d.`折后价` / d.`原价`)), 2 )) as 经销商销售价含税 ,  " +
                "		F.`集团采购价含税`, (F.经销商采购价含税 * d.数量) as 经销商采购总额,  F.`经销商采购价含税`, (f.`集团采购价含税` * d.数量) as 集团采购总额,  " +
                "		'' as 服务顾问,  '' as 零售日期,  '' as 售后进厂日期,  " +
                "		SUBSTR(a.purchase_dt, 1, 10) as 后市场采购订单日期, SUBSTR(a.purchase_dt, 11) as 后市场采购订单时间,   SUBSTR(a.`预计施工时间`, 1, 10) as 预约施工日期,  " +
                "		SUBSTR(a.`预计施工时间`, 11) as 预约施工时间,  i.`合同号` as 销售合同号,  a.实际施工日期, IF(a.数量 is NULL, d.`数量`, a.`数量`) as  数量,  " +
                "		a.备注,  f.精品编号 as `productId`,  a.Wip,  a.Service_provider as 服务商, a.refund_amount as 退款金额,  a.refund_reason as 退款理由,  " +
                "		a.refund_dt as 退款时间,   a.Is_delete, c.报价编号,  d.`编号`,  d.ID as `comboId`,  m.`产品分类`,  m.shoptypeid as 开票商品名称,  " +
                "		m.soleshopid as 企业商品编码, n.`产品种类`, o.`订单号`,  o.`订单审批通过日期`, IF(i.`交车日期` IS NULL, '否', '是') as 是否交车,  " +
                "		q.bt_status as 开票状态, q.Tickets_number as 真实发票号, q.Update_dt as 开票日期" +
                " from 报价精品详细表_精品 a left join businessticketsrely q on q.BtRely_productId=a.id " +
                "                          left join businesstickets r on r.bt_id =q.btRely_id " +
                "                          left join 用户 l on a.申请人= l.用户名, " +
                "     LSH精品套餐详细 b," +
                //修改备注：原有逻辑关联的不是最新的报价信息，现已修改(相关bug编号 5780 --路松)
                "     报价单表 mz " +
                "       JOIN ( " +
                "           SELECT " +
                "               MAX(mx.`报价编号`) 报价编号,mx.`初始报价单号` " +
                "           FROM 报价单表 mx " +
                "           WHERE mx.`是否还原点` = '是' AND mx.`是否驳回` = '否' GROUP BY mx.`初始报价单号` " +
                "       ) my ON mz.`初始报价单号` = my.`初始报价单号` " +
                "       JOIN 报价单表 c ON my.`报价编号` = c.`报价编号` " +
                " LEFT JOIN 报价交车表 i on c.交车 = i.编号" +
                "               LEFT JOIN 报价付款详细 p on c.`报价编号` = p.`报价编号` AND 付款事由= '总计'," +
                "     报价订单表 o, 报价精品详细表 d,  lsh套餐信息 e,  LSH精品信息 f, LSH库存表 g, lsh车型 h," +
                "     LSH经销商表 j, 用户 k,  LSH精品分类 m,  LSH精品种类 n " +
                " where " +
                "     a.comboId =  b.套餐编号  and a.productId = b.精品编号   and a.编号 = mz.精品  and a.编号 = d.编号" +
                "     and b.套餐编号 = d.ID  and  e.`套餐编号`=d.ID  and b.`精品编号` = f.精品编号  and c.库存编号 = g.库存编号" +
                "     and o.`订单编号` = c.`订单编号` and c.车型编号 = h.车型编号   and c.许可 = j.`许可_BMBS`  and c.销售顾问= k.用户名 " +
                "     and m.分类编号 = f.`分类编号` and m.`种类编号` = n.`种类编号`");

        Map params = Maps.newHashMap();
        if (permit.equals("LSH")) {
            sql.append(" AND a.order_status NOT IN (1, 5, 9) and a.order_status is not null  ");
        } else {
            sql.append(" and c.许可=:permit    ");
            params.put("permit", permit);
        }

        if (!Strings.isNullOrEmpty(request.purchaseSource)) {
            if (!request.purchaseSource.equals("TJLS")) {
                sql.append(" AND f.`采购来源` <> 'TJLS'  ");
            } else {
                sql.append(" AND f.`采购来源` = 'TJLS'  ");
            }
        }

        if (request.OrderId != null) {
            sql.append("  and o.`订单号` =:OrderId  ");
            params.put("OrderId", request.OrderId);
        }

        if (request.wip != null) {
            sql.append("  and a.Wip like :wip  ");
            params.put("wip", "%" + request.wip + "%");
        }

        if (request.taxNo != null) {
            sql.append("  and q.Tickets_number like :taxNo  ");
            params.put("taxNo", "%" + request.taxNo + "%");
        }

        if (request.cid != null) {
            sql.append("  and d.编号 =:cidsss  ");
            params.put("cidsss", request.cid);
        }

        if (request.comboid != null) {
            sql.append("  and d.id =:comboid  ");
            params.put("comboid", request.comboid);
        }

        if (request.id != 0) {
            sql.append("  and a.id =:id   ");
            params.put("id", request.id);
        }

        if (!Strings.isNullOrEmpty(request.idsAndticket)) {
            System.out.println(request.idsAndticket.toString());
            String[] oos = request.idsAndticket.toString().split(";");
            for (int i = 0; i < oos.length; i++) {
                if (oos[i] != null && !"".equals(oos[i])) {
                    if (i == 0) {
                        sql.append("  AND (  a.id ='" + oos[i] + "'  ");
                    } else {
                        sql.append("  or   a.id ='" + oos[i] + "' ");
                    }
                }
            }
            sql.append(" )   ");
        }

        if (!Strings.isNullOrEmpty(request.carType)) {
            sql.append("  AND (   " +
                    "      h.品牌 LIKE :car   " +
                    "      OR h.车型 LIKE :car   " +
                    "      OR h.级别 LIKE :car   " +
                    "      OR h.车型详细 LIKE :car )  ");
            params.put("car", "%" + request.carType + "%");
        }

        if (!Strings.isNullOrEmpty(request.VIN)) {
            sql.append("  AND  g.车架号  LIKE :VIN  ");
            params.put("VIN", "%" + request.VIN + "%");
        }

        if (!Strings.isNullOrEmpty(request.jingxiaoshangmingcheng)) {
            sql.append("  AND  j.经销商全称  LIKE :jingxiaoshangmingcheng ");
            params.put("jingxiaoshangmingcheng", "%" + request.jingxiaoshangmingcheng + "%");
        }

        if (!Strings.isNullOrEmpty(request.changshangmingcheng)) {
            sql.append("   AND  f.厂商 LIKE :changshangmingcheng ");
            params.put("changshangmingcheng", "%" + request.changshangmingcheng + "%");
        }

        if (!Strings.isNullOrEmpty(request.productbianhaoIds)) {
            sql.append("  AND   f.`产品编号`  LIKE :productbianhaoIds  ");
            params.put("productbianhaoIds", "%" + request.productbianhaoIds + "%");
        }

        if (!Strings.isNullOrEmpty(request.productName)) {
            sql.append("  AND   f.`产品名称`  LIKE :productName  ");
            params.put("productName", "%" + request.productName + "%");
        }

        if (!Strings.isNullOrEmpty(request.orderStatus)) {
            String[] oo = request.orderStatus.toString().split(";");
            for (int i = 0; i < oo.length; i++) {
                if (oo[i] != null && !"".equals(oo[i])) {
                    if (i == 0) {
                        if (oo[0].equals("1")) {
                            sql.append("  AND (  (a.order_status is  null or a.order_status='1' )   ");
                        } else {
                            sql.append("  AND (  a.order_status ='" + oo[i] + "'  ");
                        }
                    } else {
                        if (oo[i].equals("1")) {
                            sql.append(" or  (a.order_status is  null or a.order_status='1'  )  ");
                        } else {
                            sql.append("  or a.order_status ='" + oo[i] + "' ");
                        }
                    }
                }
            }
            sql.append(" )   ");
        }

        if (!Strings.isNullOrEmpty(request.productType)) {
            sql.append("  AND   m.`产品分类` =:productType  ");
            params.put("productType", request.productType);
        }

        if (!Strings.isNullOrEmpty(request.productClassifyTypes)) {
            sql.append("  AND   n.`产品种类`  =:productClassifyTypes  ");
            params.put("productClassifyTypes", request.productClassifyTypes);
        }

        if (!Strings.isNullOrEmpty(request.chezhuxingming)) {
            sql.append("  AND   i.`开票客户姓名`  like :chezhuxingming  ");
            params.put("chezhuxingming", "%" + request.chezhuxingming + "%");
        }
        if (!Strings.isNullOrEmpty(request.shenqignbumeng)) {
            sql.append("  AND   k.所属部门  like :shenqignbumeng  ");
            params.put("shenqignbumeng", "%" + request.shenqignbumeng + "%");
        }

        // 施工种类
        if (!Strings.isNullOrEmpty(request.shigongzhonglei)) {
            sql.append("  AND  a.施工种类   like :shigongzhonglei  ");
            params.put("shigongzhonglei", "%" + request.shigongzhonglei + "%");
        }

        if (!Strings.isNullOrEmpty(request.staryujishigongriqi)) {
            sql.append("  AND  a.`预计施工时间`   >=:staryujishigongriqi  ");
            params.put("staryujishigongriqi", request.staryujishigongriqi);
        }
        if (!Strings.isNullOrEmpty(request.endyujishigongriqi)) {
            sql.append("  AND  a.`预计施工时间`   <=:endyujishigongriqi  ");
            params.put("endyujishigongriqi", request.endyujishigongriqi);
        }

        if (!Strings.isNullOrEmpty(request.starshijishigongriqi)) {
            sql.append("  AND  a.`实际施工日期`   >=:starshijishigongriqi  ");
            params.put("starshijishigongriqi", request.starshijishigongriqi);
        }
        if (!Strings.isNullOrEmpty(request.endshijishigongriqi)) {
            sql.append("  AND  a.`实际施工日期`   <=:endshijishigongriqi  ");
            params.put("endshijishigongriqi", request.endshijishigongriqi);
        }

        if (!Strings.isNullOrEmpty(request.starcaigouriqi)) {
            sql.append("  AND a.purchase_dt  >=:starcaigouriqi  ");
            params.put("starcaigouriqi", request.starcaigouriqi);
        }
        if (!Strings.isNullOrEmpty(request.endcaigouriqi)) {
            sql.append("  AND  a.purchase_dt   <=:endcaigouriqi  ");
            params.put("endcaigouriqi", request.endcaigouriqi);
        }

        sql.append("  ORDER BY c.报价编号 DESC, d.更新时间 DESC  ");

        logger.warn(sql.toString());

        return this.getPage(sql.toString(), params, request.getPageSize(), request.getPageIndex());
    }

    @Override
    public Map getProduct(int id) {
        String sql = "select a.*,b.种类编号,b.产品分类,c.产品种类 from LSH精品信息 a\n" +
                "left join LSH精品分类 b on a.分类编号\n" +
                "left join LSH精品种类 c on b.种类编号 = c.种类编号\n" +
                "where a.精品编号 = :id";
        Map params = Maps.newHashMap();
        params.put("id", id);
        return this.getList(sql, params).size() > 0 ? (Map) this.getList(sql, params).get(0) : null;
    }

    /**
     * 根据id获取精品相关信息
     */
    @Override
    public Map findQualityInfoByFuzzyId(Integer id, String source) {
        String sql = "SELECT\n" +
                "\td.产品种类,c.产品分类,a.采购来源,a.产品名称,a.产品编号,\n" +
                "\ta.厂商,a.标配功能,a.经销商采购价未税,a.经销商销售价未税,a.单品促销状态,b.促销开始日期,\n" +
                "\tb.促销结束日期,b.经销商采购折扣率,b.经销商销售折扣率,a.功能,a.特征,\n" +
                " b.精品促销编号, "+
                " a.创建时间, "+
                " a.集团采购价未税,a.集团采购价含税,a.集团采购毛利率, "+
                "\ta.好处,a.更新账号,a.更新时间,a.精品开始日期,a.精品结束日期 \n" +
                "FROM\n" +
                "\tLSH精品信息 AS a\n" +
                "LEFT JOIN LSH精品促销 AS b ON a.精品编号 = b.精品编号\n" +
                "JOIN LSH精品分类 AS c ON a.分类编号 = c.分类编号\n" +
                "JOIN LSH精品种类 AS d ON c.种类编号 = d.种类编号\n" +
                "WHERE\n" +
                "\ta.精品编号 =:id and a.采购来源=:source ";
        Map map = Maps.newHashMap();
        map.put("id", id);
        map.put("source", source);
        return this.getList(sql, map).size() > 0 ? (Map) this.getList(sql, map).get(0) : null;
    }

    @Override
    public List isExistQualityInfo(String productNum, String source) {
        String sql = "select * from LSH精品信息 where 产品编号=:productNum and 采购来源=:source ";
        Map map = Maps.newHashMap();
        map.put("productNum", productNum);
        map.put("source", source);
        return this.getList(sql, map);
    }

    @Override
    public Map findComboInfoByProductNumAndUser(String productNum, String createUser) {
        String sql = "SELECT\t产品编号,\t产品名称,\t创建方,\t套餐开始日期,\t套餐结束日期,\t限量状态,\t限量数量,  套餐销售原价,\t套餐销售价含税,  \n" +
                "是否统一折扣,\t套餐折扣,\t套餐采购价含税,\t套餐毛利,\t套餐毛利率,\t功能,\t特征,\t好处,\t更新账号,  更新时间, 套餐编号 ,创建时间 \n" +
                "FROM\tLSH套餐信息 WHERE\t产品编号 =:productNum AND 创建方 =:createUser AND 是否套餐='是'";
        Map map = Maps.newHashMap();
        map.put("productNum", productNum);
        map.put("createUser", createUser);
        return this.getList(sql, map).size() > 0 ? (Map) this.getList(sql, map).get(0) : null;
    }

    @Override
    public List findQualityInfoByComboId(Integer comboId) {
        String sql = "SELECT f.套餐编号,a.精品编号,a.产品编号,a.产品名称,  a.厂商,  e.产品种类,  d.产品分类,  c.NC分类,  a.采购来源,  \n" +
                "a.经销商采购价含税,a.经销商销售价含税,b.精品数量,b.精品折扣,a.更新账号,a.更新时间,a.精品开始日期,a.精品结束日期 \n" +
                "FROM\tLSH精品信息  a \n" +
                "INNER JOIN LSH精品套餐详细  b ON b.精品编号 = a.精品编号 \n" +
                "INNER JOIN LSH精品NC分类  c ON c.NC编号 = a.NC编号 \n" +
                "INNER JOIN LSH精品分类  d ON d.分类编号 = a.分类编号 \n" +
                "INNER JOIN LSH精品种类  e ON e.种类编号 = d.种类编号 \n" +
                "INNER JOIN LSH套餐信息 f ON f.套餐编号=b.套餐编号\n" +
                "WHERE  b.套餐编号 =:comboId AND f.是否套餐='是' ";
        Map map = Maps.newHashMap();
        map.put("comboId", comboId);
        return this.getList(sql, map);
    }


    @Override
    public List isExistComboInfo(String productNum, String createUser) {
        String sql = "select *  from `LSH套餐信息` where 产品编号 =:productNum AND 创建方 =:createUser AND 是否套餐='是'";
        Map map = Maps.newHashMap();
        map.put("productNum", productNum);
        map.put("createUser", createUser);
        return this.getList(sql, map);
    }

    @Override
    public List getFollowCarInfoByPermit(String permit) {
        String sql = "SELECT b.来源, b.品牌, b.级别 AS 车系, b.车型, b.车型详细 AS 车款, a.A保成本, \n" +
                " a.A保MSRP AS A保市价, a.B保成本, a.B保MSRP AS  B保市价, a.逸蓝卡成本1A1B AS 逸蓝卡成本（1A1B）,\n" +
                " a.逸蓝卡MSRP1A1B AS 逸蓝卡市价（1A1B）, a.逸蓝卡成本2A2B AS 逸蓝卡成本（2A2B）, a.逸蓝卡MSRP2A2B AS 逸蓝卡市价（2A2B）, \n" +
                " a.逸蓝卡成本3A3B AS 逸蓝卡成本（3A3B）, a.逸蓝卡MSRP3A3B AS 逸蓝卡市价（3A3B）, a.尚银卡成本1年 AS 尚银卡成本（1年）, \n" +
                " a.尚银卡MSRP1年 AS 尚银卡市价（1年）, a.尚银卡成本2年 AS 尚银卡成本（2年）, a.尚银卡MSRP2年 AS 尚银卡市价（2年）, a.SSSC, \n" +
                " a.创建时间, a.更新时间, a.项目成本编号, a.更新账号 \n" +
                " FROM LSH随车项目设置  a \n" +
                " INNER JOIN LSH车型  b ON a.车型编号 = b.车型编号 \n" +
                " WHERE a.许可 = 'LSH' OR a.许可 =:permit \n" +
                " ORDER BY a.更新时间 DESC";
        Map map = Maps.newHashMap();
        map.put("permit", permit);
        return this.getList(sql, map);
    }

    @Override
    public List getAllArea() {
        String sql = "SELECT DISTINCT `大区` 区域, `大区_EN` 区域_EN FROM LSH经销商表 WHERE `大区` <> '' " +
                " UNION ALL SELECT DISTINCT `小区`, `小区_EN` FROM LSH经销商表 WHERE `小区` <> ''";
        return this.getList(sql);
    }

    @Override
    public Map findQualityInfoByProductNumAndSource(String productNum, String source) {
        String sql = "SELECT  d.产品种类,  c.产品分类,  e.NC分类, a.采购来源,  a.产品名称,  a.厂商,  \n" +
                " a.经销商采购价含税,  a.经销商销售价含税,  a.精品编号,a.更新账号,a.更新时间,a.精品开始日期,a.精品结束日期 \n" +
                " FROM LSH精品信息 AS a \n" +
                " INNER JOIN LSH精品分类 AS c ON a.分类编号 = c.分类编号 \n" +
                " JOIN LSH精品种类 AS d ON c.种类编号 = d.种类编号 \n" +
                " JOIN LSH精品NC分类 AS e ON e.NC编号 = a.NC编号\n" +
                " WHERE a.产品编号 =:productNum AND  (a.采购来源 =:source or a.采购来源 ='TJLS') ";
        Map map = Maps.newHashMap();
        map.put("productNum", productNum);
        map.put("source", source);
        return this.getList(sql, map).size() > 0 ? (Map) this.getList(sql, map).get(0) : null;
    }

    @Override
    public List getAllQualityInfoBySource(String source) {
        String sql = "SELECT a.`产品编号`, c.`产品种类`, b.`产品分类`, a.`厂商` " +
                " FROM `LSH精品信息` AS a INNER JOIN `LSH精品分类` AS b ON b.`分类编号` = a.`分类编号` " +
                " INNER JOIN `LSH精品种类` AS c ON c.`种类编号` = b.`种类编号` WHERE a.`采购来源` =:source ";
        Map map =Maps.newHashMap();
        map.put("source",source);
        return this.getList(sql,map);
    }

    @Override
    public List isExistfallowCar(String permit, String startTime, String endTime, Integer carId, Integer proId) {
        String sql = "select *  from lsh随车项目设置 where 许可=:permit and 车型编号=:carId " +
                " and ((:startTime>=启用时间 and :startTime <=停用时间) OR ( :endTime>=启用时间 and :endTime <=停用时间)) ";
        Map map = Maps.newHashMap();
        map.put("permit", permit);
        map.put("startTime", startTime);
        map.put("endTime", endTime);
        map.put("carId", carId);
        if (proId != null) {
            sql += " and 项目成本编号 <>:proId";
            map.put("proId", proId);
        }
        return this.getList(sql, map);
    }



   //根据 精品编号 获取 到 精品信息
   public Lsh精品信息Model getAfterMarketOrdersProductById(int productId){

       String sql = " SELECT * from `lsh精品信息` where `精品编号`= :productId  " ;
       Map map = Maps.newHashMap();
       map.put("productId", productId);
       List list= this.getList(sql, map);

           return (Lsh精品信息Model)list.get(0);


   }


//  返回 精品列表  获取的是 精品分类的 信息  筛选条件 需要分页
    public  PageData getAfterMarketProductTypeAndDetail(int pageSize, int pageIndex,  String type){
        StringBuilder sql = new StringBuilder();
        sql.append("   select a.`产品分类`,b.`产品名称` from   `lsh精品分类` a  " +
                "   LEFT JOIN   `lsh精品信息` b   " +
                "  on b.`分类编号`=a.`分类编号`  where 1=1     ");

        Map<String, String> params = Maps.newHashMap();
        if (!Strings.isNullOrEmpty(type)) {
            sql.append(" AND a.`分类编号` = :type ");
            params.put("type", type);
        }
        List ss=this.getList(sql.toString(),params);
        PageData pg=new PageData(ss,ss.size());
      return pg;
    }


    // 获得 的是  接收人user
    public List getuserMessages(){

        StringBuilder sql = new StringBuilder();
        sql.append("   select  `user`  " +
                "from message_setting " +
                "where permit='LSH' and type=5   ");

        Map<String, String> params = Maps.newHashMap();
        List ss=this.getList(sql.toString(),params);
        return ss;
    }

    // 获取的  是 报价精品详细表
    public List getSelfByidAndComId(int cid,int id){
        StringBuilder sql = new StringBuilder();
        sql.append("   select * from `报价精品详细表` a   " +
                "   where a.编号=:id   " +
                "   and ID=:cid    ");
        String cids=cid+"";
        String ids=id+"";
        Map<String, String> params = Maps.newHashMap();
        params.put("cid",cids);
        params.put("id",ids);

        List ss=this.getList(sql.toString(),params);
        return ss;


    }

    // 获得 list 套餐信息
     public  List findDetailtaocangDetailBytwoId(int idsss){

         StringBuilder sql = new StringBuilder();
         sql.append("  select b.`套餐编号` from `lsh精品套餐详细`  a\n" +
                 "LEFT JOIN `lsh套餐信息` b on a.`套餐编号`=b.`套餐编号`\n" +
                 "where  a.`精品编号`=?id and b.`是否套餐`='否'  ");
         String id=idsss+"";
         Map<String, String> params = Maps.newHashMap();
         params.put("id",id);
         List ss=this.getList(sql.toString(),params);
         return ss;

    }

// 新增的 11.10 下午 15：12
   public Map getdingdanbianhao(String id,String permit){

       StringBuilder sql = new StringBuilder();
       sql.append("  SELECT " +
               "    IF(k.`交车日期` IS NULL,'否', '是') 是否交车,  " +
               "  a.`报价编号`  " +
               "   FROM  " +
               "  `报价单表` a " +
               "    LEFT JOIN 报价交车表 k ON a.交车 = k.编号       " +
                  "WHERE  " +
                   "  a.`订单编号` in (  " +
                       "    SELECT  " +
                       "      b.`订单编号`  " +
                       "    FROM  " +
                       "      `报价订单表` b  " +
                       "    WHERE  " +
                       "      b.`订单号` = :ids )  " +
                       "AND a.`是否还原点` = '是'  " +
                       "AND a.`许可` = :permit  order by a.`报价编号` desc LIMIT 1  ");
       String ids=id+"";
       Map<String, String> params = Maps.newHashMap();
       params.put("ids",ids);
       params.put("permit",permit);

       List sss = this.getList(sql.toString(), params);
       Map newtem = new HashMap();
       if (sss.size() > 0) {
           newtem = (Map) sss.get(0);

       }else{
           newtem.put("报价编号", null);
           newtem.put("是否交车", "否");

       }


       return newtem;

   }

    // 获得 list 套餐信息
   public List findDetailtaocangDetailBytwoId(String idsss){
       StringBuilder sql = new StringBuilder();
       sql.append("  SELECT a.* from lsh精品套餐详细 a " +
               "  LEFT JOIN `lsh套餐信息` b on  a.`套餐编号`=b.`套餐编号`  " +
               "   where b.`是否套餐`='否' and a.精品编号=:ids   ");

       Map<String, String> params = Maps.newHashMap();
       params.put("ids",idsss);


       List ss=this.getList(sql.toString(),params);

        return ss;
    }





// 获得 是   所有的 店的  精品id
    public List getAfterMarketShopAllId(String ids){
        StringBuilder sql = new StringBuilder();
        sql.append("  select a.BtRely_productId as 精品id,  " +
                "  a.bt_trueIds as 经销商id   " +
                "  from businessticketsrely a where a.bt_trueIds=:ids  ");

        Map<String, String> params = Maps.newHashMap();
        params.put("ids",ids);
        List ss=this.getList(sql.toString(),params);
        return ss;
    }


   public   List getAfterMarketShopAllbusiness(String ids){
       StringBuilder sql = new StringBuilder();
       sql.append("  select  * from businesstickets a where  a.bt_taxNo=:ids ");

       Map<String, String> params = Maps.newHashMap();
       params.put("ids",ids+"");
       List ss=this.getList(sql.toString(),params);
       return ss;


   }

    public PageData getBusinessAlllist(int pageSize, int pageIndex) {

        StringBuilder sql = new StringBuilder();
        sql.append("  select * from businesstickets  a  order by a.bt_updateDt  desc    ");

        Map<String, String> params = Maps.newHashMap();
        return this.getPage(sql.toString(), params, pageSize, pageIndex);

    }


    public List getBbmodelListByBtTaxNo(List<AfterMarketService.getBusinessticketsStatus> relist) {
        return null;
    }

    public List getBusniessByName(List relist) {
        StringBuilder sql = new StringBuilder();
        sql.append("  select * from businesstickets a where 1=1 and a.Bt_starStatus='启用'   ");
        for (int i = 0; i < relist.size(); i++) {
            String mei = relist.get(i).toString();
            if (mei != null && !mei.equals("")) {
                if (i == 0) {
                    sql.append(" and (a.bt_name='" + mei + "'  ");
                } else {

                    sql.append(" or a.bt_name='" + mei + "'  ");
                }
            }

        }
        sql.append(") ");
        return this.getList(sql.toString());
    }


    // 拿出 所有的额 大区 不重复 11.26
    public List getAfterMarketWeekDailyDetailOneDaqu() {

        StringBuilder sql = new StringBuilder();
        sql.append(" select DISTINCT b.大区_EN  " +
                "         from   " +
                "  LSH经销商表 b  where  (b.大区_EN is not  null and b.大区_EN <>' ' )  ");
        return this.getList(sql.toString());

    }


    // 拿出 所有的 大区的数据  11.26
    public List getAfterMarketWeekDailyDetailOne(String years, String monthss, String daqu) {

        StringBuilder sql = new StringBuilder();
        sql.append(" select   " +
                "count(1) as '新车销量'  from   " +
                "(  " +
                "select * from 报价单表   " +
                " where 报价编号 in (  " +
                "  select   max(b.报价编号)    " +
                "  from lsh库存表 a    " +
                "  join 报价单表 b on a.库存编号 = b.库存编号   " +
                "   where b.是否驳回 = '否'  " +
                "     and a.`车辆零售状态` <> '未卖' and a.车架号 not like 'iris'  " +
                "    GROUP BY b.初始报价单号   " +
                "   )   " +
                ") a  " +
                "join 报价交车表 d on a.交车 = d.`编号`   " +
                "where    " +
                "d.退车日期 is null  " +
                "and a.许可 IN (SELECT 许可_bmbs FROM LSH经销商表 WHERE 大区_en =:daqu )  " +
                "AND  " +
                "MONTH(d.开票时间)=:monthss    " +
                "and  " +
                "year(d.开票时间)=:years   ");

        Map<String, String> params = Maps.newHashMap();
        params.put("daqu", daqu);
        params.put("years", years);
        params.put("monthss", monthss);
        return this.getList(sql.toString(), params);

    }


    // 拿出 所有的 大区的 后市场的 数据
    public List getAfterMarketWeekDailyDetailtwos(String year, String monthss, String daqu) {

        StringBuilder sql = new StringBuilder();
        sql.append(" select    " +
                "   sum( a.数量 ) as  数量,  c.产品名称  " +
                "from   " +
                "lsh精品信息 c join( " +
                "  select  " +
                "    a.编号,a.productId,a.数量   " +
                "   from   " +
                "  报价精品详细表_精品 a    " +
                "  where a.purchase_dt is not null  " +
                "  and MONTH(a.purchase_dt)=:monthss  and YEAR(a.purchase_dt)=:year   " +
                "  and a.编号 in(  " +
                "   select b.精品 from 报价单表 b where b.许可 in(  " +
                "      select f.许可_bmbs from lsh经销商表 f where f.大区_en=:daqu    )  " +
                "        )  " +
                "    ) a  on c.精品编号=a.productId    " +
                "GROUP BY c.产品名称     ");

        Map<String, String> params = Maps.newHashMap();
        params.put("daqu", daqu);
        params.put("year", year);
        params.put("monthss", monthss);
        return this.getList(sql.toString(), params);

    }

    public List getplistbysole() {

        StringBuilder sql = new StringBuilder();
        sql.append(" select DISTINCT 厂商 from lsh精品信息 a where a.厂商 is not null and a.厂商 <>''   ");

        Map<String, String> params = Maps.newHashMap();

        return this.getList(sql.toString(), params);
    }


    public List getPurchasedProductList(String year, String monthss) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select DISTINCT a.productId from   " +
                "报价精品详细表_精品 a   " +
                "   where a.purchase_dt is not null and  " +
                "  MONTH(a.purchase_dt)=:monthss  and YEAR(a.purchase_dt)=:year     ");

        Map<String, String> params = Maps.newHashMap();
        params.put("year", year);
        params.put("monthss", monthss);
        return this.getList(sql.toString(), params);
    }

    public List getplists(String daqu) {

        StringBuilder sql = new StringBuilder();
        sql.append(" select  " +
                "    DISTINCT 许可_lsh as permits " +
                "    from  " +
                "    lsh经销商表 f  " +
                "    where f.大区_en=:daqu   ");
        Map<String, String> params = Maps.newHashMap();
        params.put("daqu", daqu);
        return this.getList(sql.toString(), params);
    }


    public List getPurchasedProductList4Dealer(String year, String month) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT c.`大区`, c.`大区_EN`, c.`许可_LSH`, C.许可_BMBS, ");
        sql.append(" d.精品编号, IFNULL(d.产品名称, d.产品编号) 产品名称, sum(a.数量) as 集采采购数量,IFNULL(d.集团采购价含税,0) as 单车采购成本,  sum(a.数量)*IFNULL(d.集团采购价含税,0) as  集采总成本 ");
        sql.append(" from 报价精品详细表_精品 a, 报价单表 b, lsh经销商表 c, lsh精品信息 d ");
        sql.append(" where DATE_FORMAT(a.purchase_dt, '%Y%m')=:year_month ");
        sql.append(" and a.编号=b.精品 and b.许可=c.许可_bmbs and d.精品编号=a.productid and d.产品名称 is not null ");
        sql.append(" group by c.`许可_LSH`, d.精品编号");
        sql.append(" order by c.`大区`, c.`许可_LSH` ");

        Map<String, String> params = Maps.newHashMap();
        params.put("year_month", year + month);
        return this.getList(sql.toString(), params);
    }

    public List getAfterMarkettwoWeekDailyDetailtwoThree(String permit, String years, String months) {

        StringBuilder sql = new StringBuilder();
        sql.append("   select count(1) as '新车销量'  from     " +
                "                (   select * from 报价单表    " +
                "                 where 报价编号 in (   " +
                "                  select   max(b.报价编号)    " +
                "                  from lsh库存表 a      " +
                "                  join 报价单表 b on a.库存编号 = b.库存编号    " +
                "                   where b.是否驳回 = '否'   " +
                "                     and a.`车辆零售状态` <> '未卖' and a.车架号 not like 'iris'    " +
                "                    GROUP BY b.初始报价单号    )     " +
                "                ) a    " +
                "                join 报价交车表 d on a.交车 = d.`编号`    " +
                "                where   d.退车日期 is null   " +
                "                and a.许可 IN (SELECT 许可_bmbs FROM LSH经销商表 WHERE 许可_LSH=:permit )    " +
                "                AND    " +
                "                MONTH(d.开票时间)=:months     " +
                "                and    " +
                "                year(d.开票时间)=:years    ");

        Map<String, String> params = Maps.newHashMap();

        params.put("months", months);
        params.put("years", years);
        params.put("permit", permit);

        return this.getList(sql.toString(), params);


    }

    public List getAfterMarkettwoWeekDailyDetailtwoThrees(String permit, String years, String months, String prodsonid) {
        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT if((d.数量*s.集团采购价含税)=0,0,(d.数量*s.集团采购价含税) )as 集采总成本 ,  " +
                "                     d.productId ,d.数量 as 集采采购数量 , f.`许可_LSH`,     " +
                "                     s.集团采购价含税 as 单车采购成本 ,     " +
                "                     f.大区_en     " +
                "                     from      " +
                "                       lsh经销商表 f     " +
                "                     join      " +
                "                      (     " +
                "                       select c.许可 ,g.数量,g.productId     " +
                "                          from  报价单表  c  join  (     " +
                "                         select a.编号,sum(a.`数量`) as 数量,a.productId  from  报价精品详细表_精品  a     " +
                "                             where a.productId  =:prodsonid and MONTH(a.purchase_dt)=:months       " +
                "                             and YEAR(a.purchase_dt)=:years    " +
                "                         )  g on g.编号=c.精品    " +
                "                     ) d on d.许可=f.许可_bmbs      " +
                "                     join lsh精品信息 s on s.精品编号=d.productid    " +
                "                     where f.`许可_LSH`=:permit     ");

        Map<String, String> params = Maps.newHashMap();

        params.put("months", months);
        params.put("years", years);
        params.put("permit", permit);
        params.put("prodsonid", prodsonid);
        return this.getList(sql.toString(), params);


    }


    public List getAfterMarkettwoWeekDailyDetailtwosons(String years, String months, String permit, String productid) {

        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT if((sum(d.数量)*s.集团采购价含税)=0,0,(d.数量*s.集团采购价含税) )as 集采总成本 ,  " +
                "                     d.数量 as 集采采购数量 ,     " +
                "                     s.集团采购价含税 as 单车采购成本     " +
                "                     from      " +
                "                       lsh经销商表 f     " +
                "                     join      " +
                "                      (     " +
                "                       select c.许可 ,g.数量,g.productId     " +
                "                          from  报价单表  c  join  (     " +
                "                         select a.编号, a.`数量` as 数量,a.productId  from  报价精品详细表_精品  a     " +
                "                             where a.productId  =:productid  and MONTH(a.purchase_dt)=:months       " +
                "                             and YEAR(a.purchase_dt)=:years    " +
                "                         )  g on g.编号=c.精品    " +
                "                     ) d on d.许可=f.许可_bmbs      " +
                "                     join lsh精品信息 s on s.精品编号=d.productid    " +
                "                     where f.`许可_LSH`=:permit     ");

        Map<String, String> params = Maps.newHashMap();

        params.put("months", months);
        params.put("years", years);
        params.put("permit", permit);
        params.put("productid", productid);
        return this.getList(sql.toString(), params);


    }


    public List getAfterMarkettwoWeekDailyDetailtwosonstongxue(String years, String months, String permit, String productid) {

        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT if((sum(d.数量)*s.集团采购价含税)=0,0,(d.数量*s.集团采购价含税) )as 集采总成本 ,   \n" +
                "                                     d.数量 as 集采采购数量 ,      \n" +
                "                                     s.集团采购价含税 as 单车采购成本      \n" +
                "                                     from   (     " +
                "                                       select c.许可 ,g.数量,g.productId      \n" +
                "                                          from  报价单表  c  join  (      \n" +
                "                                         select a.编号, sum(a.`数量`) as 数量,a.productId  from  报价精品详细表_精品  a    \n" +
                "                                         join 报价单表 h on h.精品=a.编号 join   lsh经销商表 i on h.许可=i.许可_bmbs     \n" +
                "                                             where a.productId  =:productid  and MONTH(a.purchase_dt)=:months      \n" +
                "                                             and YEAR(a.purchase_dt)=:years  and i.`许可_LSH`=:permit    \n" +
                "                                         )  g on g.编号=c.精品     ) d     \n" +
                "                                     join lsh精品信息 s on s.精品编号=d.productid       ");

        Map<String, String> params = Maps.newHashMap();

        params.put("months", months);
        params.put("years", years);
        params.put("permit", permit);
        params.put("productid", productid);
        return this.getList(sql.toString(), params);


    }


    public List getAfterMarkettwoWeekDailyDetailtwosonsbysole(String years, String months, String permit, String sole) {

        StringBuilder sql = new StringBuilder();
        sql.append("  select sum(a.数量) as 数量,s.`厂商`,f.许可_lsh as permit   " +
                "from 报价精品详细表_精品  a   " +
                "join ( select s.精品编号 from  " +
                "  lsh精品信息 s  where s.`厂商`=:sole   " +
                "   ) b  on a.productid=b.精品编号  " +
                "join  " +
                "      lsh精品信息 s on s.精品编号=a.productid  " +
                "join    " +
                "   报价单表 x on x.精品=a.编号  " +
                "JOIN lsh经销商表 f on f.许可_bmbs=x.许可  " +
                "  where  MONTH(a.purchase_dt)=:months    " +
                "   and YEAR(a.purchase_dt)=:years  " +
                "and f.`许可_lsh`=:permit     ");

        Map<String, String> params = Maps.newHashMap();

        params.put("months", months);
        params.put("years", years);
        params.put("permit", permit);
        params.put("sole", sole);
        return this.getList(sql.toString(), params);


    }

    public PageData getAfterMarketWeekonestwo(int sizes, int b) {

        StringBuilder sql = new StringBuilder();
        sql.append("  SELECT GROUP_CONCAT(a.`分类编号`) as 精品分类 ,a.shoptypeid as 开票商品名称,    " +
                "a.soleshopid 企业商品编码    \n" +
                "from lsh精品分类 a    \n" +
                "where a.shoptypeid is not null and a.shoptypeid<>''  " +
                " and a.soleshopid is not null and a.soleshopid<>''  \n" +
                "GROUP BY a.shoptypeid ,a.soleshopid   ");


        return this.getPage(sql.toString(), sizes, b);

    }

    public List getAfterMarketWeekonestwos() {
        StringBuilder sql = new StringBuilder();
        sql.append("  select    \n" +
                "a.`分类编号` ,a.`产品分类`,b.`产品种类`   from LSH精品分类 a    \n" +
                "join lsh精品种类 b on a.种类编号=b.种类编号     ");
        return this.getList(sql.toString());

    }

    public List findAllbysqland() {
        StringBuilder sql = new StringBuilder();
        sql.append("select a.* ,b.`产品种类` from  \n" +
                " lsh精品分类 a JOIN  \n" +
                "lsh精品种类  b on a.`种类编号`=b.`种类编号`   ");
        return this.getList(sql.toString());
    }

    //很明显 又有很多的 坑
    public PageData getVendorMailList(int index, int size) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from t_prd_supplier_mail_setting  a " +
                "  where a.IS_DELETED=0   order by a.UPDATE_DATE desc  ");
        return this.getPage(sql.toString(), size, index);
    }

    @Override
    public List getProductQuotation(Integer productId) {
        String sql = "SELECT e.`编号`,e.ID AS 套餐id " +
                " FROM `报价精品详细表` e " +
                " WHERE EXISTS (" +
                "   SELECT d.套餐编号 FROM (" +
                "      SELECT c.套餐编号 FROM `lsh精品信息` a" +
                "      JOIN `lsh精品套餐详细` b ON a.精品编号 = b.精品编号 " +
                "      JOIN `lsh套餐信息` c ON b.`套餐编号` = c.`套餐编号` " +
                "      WHERE c.是否套餐 = '否' " +
                "      AND a.`精品编号` = :productId" +
                "   ) d WHERE e.id = d.套餐编号" +
                ")";
        Map<String,String> params = new HashMap<>();
        params.put("productId",productId.toString());
        return this.getList(sql,params);
    }

    @Override
    public List getProductPackageDetail(Integer productId) {
        String sql = "SELECT b.* FROM `lsh精品信息` a " +
                " JOIN `lsh精品套餐详细` b ON a.精品编号 = b.精品编号 " +
                " WHERE a.`精品编号` = :productId";
        Map<String,String> params = new HashMap<>();
        params.put("productId",productId.toString());
        return this.getList(sql,params);
    }

    @Override
    public List getPackageInfo(Integer productId) {
        String sql = "SELECT c.* FROM `lsh精品信息` a " +
                " JOIN `lsh精品套餐详细` b ON a.精品编号 = b.精品编号 " +
                " JOIN `lsh套餐信息` c ON b.`套餐编号` = c.`套餐编号` " +
                " WHERE c.是否套餐 = '否' \n" +
                " AND a.`精品编号` = :productId";
        Map<String,String> params = new HashMap<>();
        params.put("productId",productId.toString());
        return this.getList(sql,params);
    }
}
